package com.joinval.common.utils;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.entity.vo.TemplateExcelConstants;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.view.PoiBaseView;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.ui.ModelMap;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.stream.FileImageInputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

public class ExcelUtil {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);


    /**
     * 模板路径
     */
    private static final String TEMPLATE_PATH = "templates/";

    /**
     * 生成excel对象
     *
     * @param params       模板导出参数设置
     * @param data         模板导出数据
     * @param templateName 模板名称
     * @return workBook对象
     * @throws Exception 异常抛出
     */
    public static Workbook getWorkbook(TemplateExportParams params, Map<String, Object> data, String templateName) throws Exception {
        String templatePath = TEMPLATE_PATH + templateName;
        File file = getTemplateFile(templatePath);
        params.setTemplateUrl(file.getAbsolutePath());
        //params.setTemplateUrl("/Users/chenkaihang/IdeaProjects/yize-volunteer/src/main/resources/templates/volExchangeRecord.xlsx");
        Workbook book = ExcelExportUtil.exportExcel(params, data);
        if (file.exists()) {
            file.delete();
        }
        return book;
    }

    /**
     * 图片到byte数组
     * @param path
     * @return
     */
    public static byte[] image2byte(String path){
        byte[] data = null;
        FileImageInputStream input = null;
        try {
            input = new FileImageInputStream(new File(path));
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            byte[] buf = new byte[1024];
            int numBytesRead = 0;
            while ((numBytesRead = input.read(buf)) != -1) {
                output.write(buf, 0, numBytesRead);
            }
            data = output.toByteArray();
            output.close();
            input.close();
        }
        catch (FileNotFoundException ex1) {
            ex1.printStackTrace();
        }
        catch (IOException ex1) {
            ex1.printStackTrace();
        }
        return data;
    }
    /**
     * 获取 文件 流
     * @param url
     * @return
     * @throws IOException
     */
    public static byte[] getFile(String url) throws IOException{
        URL urlConet = new URL(url);
        HttpURLConnection con = (HttpURLConnection)urlConet.openConnection();
        con.setRequestMethod("GET");
        con.setConnectTimeout(4 * 1000);
        InputStream inStream = con .getInputStream();    //通过输入流获取图片数据
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[2048];
        int len = 0;
        while( (len=inStream.read(buffer)) != -1 ){
            outStream.write(buffer, 0, len);
        }
        inStream.close();
        byte[] data =  outStream.toByteArray();
        return data;
    }
    /**
     * 导出excel公共方法
     * @param templateName
     * @param data
     * @param exportFileName
     * @param request
     * @param response
     */
    public static void exportExcelReport(
            String templateName,//临时模版文件名
            Map<String, Object> data,//待写入excel数据
            String exportFileName,//导出文件名称
            HttpServletRequest request,
            HttpServletResponse response){

        String templatePath = TEMPLATE_PATH + templateName;
        File file = null;
        try {
            file = getTemplateFileNew(templatePath);
            TemplateExportParams templateExportParams = new TemplateExportParams(file.getAbsolutePath());


            ModelMap modelMap = new ModelMap();
            modelMap.put(TemplateExcelConstants.MAP_DATA, data);
            modelMap.put(TemplateExcelConstants.PARAMS, templateExportParams);
            modelMap.put(TemplateExcelConstants.FILE_NAME, exportFileName);

            //渲染
            PoiBaseView.render(
                    modelMap,
                    request,
                    response, TemplateExcelConstants.EASYPOI_TEMPLATE_EXCEL_VIEW);

            //删除临时文件
            if (file.exists())
                file.delete();

        } catch (Exception e) {
            log.info("导出报表异常：",e);
        }finally {
            file.delete();
        }
    }

    /**
     *  // 加载模板
     *         TemplateExportParams params = new TemplateExportParams("C:\\Users\\admin\\Downloads\\templateMap.xls");
     *         Map<String, Object> map = new HashMap<>(16);
     *         map.put("title", "全亚洲,最帅气人员名单");
     *         map.put("date", "2018-12-05");
     *         map.put("interviewer", "JustryDeng");
     *         List<Map<String, Object>> list = new ArrayList<>(16);
     *         Map<String, Object> tempMap;
     *         for (int i = 0; i < 5; i++) {
     *             tempMap = new HashMap<>();
     *             tempMap.put("name", "邓沙利文");
     *             tempMap.put("gender", new Random().nextInt(2) == 0 ? "男" : "女");
     *             tempMap.put("age", new Random().nextInt(90) + 11);
     *             tempMap.put("hobby", "活的，女的！！！");
     *             tempMap.put("handsomeValue", "100分(满分100分)");
     *             tempMap.put("motto", "只所以只帅到了全亚洲，是因为其他地方审美不同！");
     *             list.add(tempMap);
     *         }
     *         map.put("dataList", list);
     *         // 生成workbook 并导出
     *         Workbook workbook = ExcelExportUtil.exportExcel(params, map);
     *         File savefile = new File("C:\\Users\\admin\\Desktop\\excelTest");
     *         if (!savefile.exists()) {
     *             boolean result = savefile.mkdirs();
     *             System.out.println("目录不存在,进行创建,创建" + (result ? "成功!" : "失败！"));
     *         }
     *         FileOutputStream fos = new FileOutputStream("C:\\Users\\admin\\Desktop\\excelTest\\templateMapResult.xls");
     *         workbook.write(fos);
     *         fos.close();
     */

    /**
     * 获取模板文件--获取到的文件为临时文件，用完后需要手动删除
     * <p>由于springboot打包成jar之后，不能以绝对路径的形式读取模板文件，故此处将模板文件以临时文件的形式写到磁盘中，用完请手动删除</p>
     *
     * @param templatePath 模板路径
     * @return 模板文件
     * @throws Exception 异常抛出
     */
    public static File getTemplateFileNew(String templatePath) throws Exception {
        File file = File.createTempFile("template", ".xlsx");
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(templatePath);
        if (resources.length == 1) {
            InputStream inputStream = resources[0].getInputStream();
            inputStreamToFile(inputStream, file);
        } else {
            throw new RuntimeException("请检查文件模板是否存在！");
        }
        return file;
    }

    /**
     * 获取模板文件--获取到的文件为临时文件，用完后需要手动删除
     * <p>由于springboot打包成jar之后，不能以绝对路径的形式读取模板文件，故此处将模板文件以临时文件的形式写到磁盘中，用完请手动删除</p>
     *
     * @param templatePath 模板路径
     * @return 模板文件
     * @throws Exception 异常抛出
     */
    public static File getTemplateFile(String templatePath) throws Exception {
        File file = File.createTempFile("template", ".xlsx");
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(templatePath);
        if (resources.length == 1) {
            InputStream inputStream = resources[0].getInputStream();
            inputStreamToFile(inputStream, file);
        } else {
            throw new RuntimeException("请检查文件模板是否存在！");
        }
        return file;
    }

    /**
     * InputStream 转file
     *
     * @param ins  输入流
     * @param file 目标文件
     */
    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 对象转换为Map<String, Object>的工具类
     *
     * @param obj
     *            要转换的对象
     * @return map
     * @throws IllegalAccessException
     */
    private static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
        Map<String, Object> map = new HashMap<>(16);
        Class<?> clazz = obj.getClass();
        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            String fieldName = field.getName();
            /*
             * Returns the value of the field represented by this {@code Field}, on the
             * specified object. The value is automatically wrapped in an object if it
             * has a primitive type.
             * 注:返回对象该该属性的属性值，如果该属性的基本类型，那么自动转换为包装类
             */
            Object value = field.get(obj);
            map.put(fieldName, value);
        }
        return map;
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return list;
    }
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("[monitor][表单功能]", e);
        }
        return list;
    }


    public static void export(HttpServletResponse response, Workbook workbook, String fileName) throws IOException {
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "iso8859-1"));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(response.getOutputStream());
        workbook.write(bufferedOutPut);
        bufferedOutPut.flush();
        bufferedOutPut.close();

    }
    /**
     * 图片格式化，Word导出图片格式
     *
     * @param imgPath 图片路径
     */
    public static ImageEntity imgFormatting(String imgPath, int width, int height) {
        //设置图片
        ImageEntity image = new ImageEntity(imgPath, width, height);
        //表格外添加简单图片
//        image.setType(ImageEntity.URL);
        return image;
    }
}
